{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT within SELECT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Bigger than Russia\n",
    "\n",
    "**List each country name where the population is larger than that of 'Russia'.**\n",
    "\n",
    "```\n",
    "world(name, continent, area, population, gdp)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "world = pd.read_sql_table('world', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Bangladesh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>Indonesia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>Nigeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>Pakistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name\n",
       "13      Bangladesh\n",
       "23          Brazil\n",
       "35           China\n",
       "76           India\n",
       "77       Indonesia\n",
       "125        Nigeria\n",
       "130       Pakistan\n",
       "185  United States"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bm = world.loc[world['name']=='Russia', 'population'].values[0]\n",
    "world.loc[world['population'] > bm, ['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Richer than UK\n",
    "\n",
    "**Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.**\n",
    "\n",
    "> _Per Capita GDP_   \n",
    "> The per capita GDP is the gdp/population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Austria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Denmark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Finland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>Iceland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Ireland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Liechtenstein</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Luxembourg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Monaco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Norway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>San Marino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>Switzerland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name\n",
       "3          Andorra\n",
       "9          Austria\n",
       "16         Belgium\n",
       "46         Denmark\n",
       "59         Finland\n",
       "64         Germany\n",
       "75         Iceland\n",
       "80         Ireland\n",
       "97   Liechtenstein\n",
       "99      Luxembourg\n",
       "112         Monaco\n",
       "121    Netherlands\n",
       "128         Norway\n",
       "147     San Marino\n",
       "166         Sweden\n",
       "167    Switzerland"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world['pcgdp'] = world['gdp']/world['population']\n",
    "bm = world.loc[world['name']=='United Kingdom', 'pcgdp'].values[0]\n",
    "world.loc[(world['pcgdp']>bm) & (world['continent']=='Europe'), ['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Neighbours of Argentina and Australia\n",
    "\n",
    "\n",
    "List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>continent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Australia</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Bolivia</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Chile</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Colombia</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Ecuador</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Fiji</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Guyana</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Kiribati</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Marshall Islands</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>Nauru</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Palau</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Paraguay</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>Peru</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>Saint Vincent and the Grenadines</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>Samoa</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>Solomon Islands</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>Suriname</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>Tonga</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>Tuvalu</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Vanuatu</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 name      continent\n",
       "6                           Argentina  South America\n",
       "8                           Australia        Oceania\n",
       "20                            Bolivia  South America\n",
       "23                             Brazil  South America\n",
       "34                              Chile  South America\n",
       "36                           Colombia  South America\n",
       "50                            Ecuador  South America\n",
       "58                               Fiji        Oceania\n",
       "71                             Guyana  South America\n",
       "88                           Kiribati        Oceania\n",
       "106                  Marshall Islands        Oceania\n",
       "119                             Nauru        Oceania\n",
       "122                       New Zealand        Oceania\n",
       "131                             Palau        Oceania\n",
       "133                  Papua New Guinea        Oceania\n",
       "134                          Paraguay  South America\n",
       "135                              Peru  South America\n",
       "145  Saint Vincent and the Grenadines  South America\n",
       "146                             Samoa        Oceania\n",
       "157                   Solomon Islands        Oceania\n",
       "165                          Suriname  South America\n",
       "175                             Tonga        Oceania\n",
       "180                            Tuvalu        Oceania\n",
       "186                           Uruguay  South America\n",
       "188                           Vanuatu        Oceania\n",
       "190                         Venezuela  South America"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[world['continent'].isin(\n",
    "    world.loc[world['name'].isin(['Argentina', 'Australia']), 'continent']), \n",
    "          ['name', 'continent']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Between Canada and Poland\n",
    "\n",
    "Which country has a population that is more than Canada but less than Poland? Show the name and the population."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [name, population]\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[(world['population']>\n",
    "    world.loc[world['name']=='Canada', 'population'].values[0]) &\n",
    "    (world['population']<world.loc[world['name']=='Poland', 'population'].values[0]), \n",
    "          ['name', 'population']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Percentages of Germany\n",
    "\n",
    "Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.\n",
    "\n",
    "**Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.**\n",
    "\n",
    "The format should be Name, Percentage for example:\n",
    "\n",
    "name\t| percentage\n",
    "--------|-----------\n",
    "Albania\t| 3%\n",
    "Andorra\t| 0%\n",
    "Austria\t| 11%\n",
    "...\t| ...\n",
    "\n",
    "> _Decimal places_   \n",
    "> You can use the function ROUND to remove the decimal places.\n",
    "\n",
    "> _Percent symbol %_\n",
    "> You can use the function CONCAT to add the percentage symbol.\n",
    "\n",
    "[To get a well rounded view of the important features of SQL you should move on to the next tutorial concerning aggregates.](https://sqlzoo.net/wiki/SUM_and_COUNT)\n",
    "\n",
    "To gain an absurdly detailed view of one insignificant feature of the language, read on.\n",
    "\n",
    "We can use the word `ALL` to allow >= or > or < or <=to act over a list. For example, you can find the largest country in the world, by population with this query:\n",
    "\n",
    "```sql\n",
    "SELECT name\n",
    "  FROM world\n",
    " WHERE population >= ALL(SELECT population\n",
    "                           FROM world\n",
    "                          WHERE population>0)\n",
    "```\n",
    "\n",
    "You need the condition **population>0** in the sub-query as some countries have **null** for population."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Austria</td>\n",
       "      <td>11%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Belarus</td>\n",
       "      <td>11%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>14%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Bosnia and Herzegovina</td>\n",
       "      <td>4%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Bulgaria</td>\n",
       "      <td>8%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Croatia</td>\n",
       "      <td>5%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Denmark</td>\n",
       "      <td>7%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Estonia</td>\n",
       "      <td>2%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Finland</td>\n",
       "      <td>7%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>France</td>\n",
       "      <td>81%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Germany</td>\n",
       "      <td>100%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Greece</td>\n",
       "      <td>13%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>Hungary</td>\n",
       "      <td>12%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>Iceland</td>\n",
       "      <td>0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Ireland</td>\n",
       "      <td>6%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>Italy</td>\n",
       "      <td>72%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Kazakhstan</td>\n",
       "      <td>22%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Latvia</td>\n",
       "      <td>2%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Liechtenstein</td>\n",
       "      <td>0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Lithuania</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Luxembourg</td>\n",
       "      <td>1%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Malta</td>\n",
       "      <td>1%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>Moldova</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Monaco</td>\n",
       "      <td>0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Montenegro</td>\n",
       "      <td>1%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>21%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>North Macedonia</td>\n",
       "      <td>2%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Norway</td>\n",
       "      <td>6%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Poland</td>\n",
       "      <td>46%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>12%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Romania</td>\n",
       "      <td>23%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>San Marino</td>\n",
       "      <td>0%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>Serbia</td>\n",
       "      <td>8%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>Slovakia</td>\n",
       "      <td>7%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>Slovenia</td>\n",
       "      <td>3%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Spain</td>\n",
       "      <td>57%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>12%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>10%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>50%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>80%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>Vatican City</td>\n",
       "      <td>0%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       name   pct\n",
       "1                   Albania    3%\n",
       "3                   Andorra    0%\n",
       "9                   Austria   11%\n",
       "15                  Belarus   11%\n",
       "16                  Belgium   14%\n",
       "21   Bosnia and Herzegovina    4%\n",
       "25                 Bulgaria    8%\n",
       "42                  Croatia    5%\n",
       "45           Czech Republic   13%\n",
       "46                  Denmark    7%\n",
       "55                  Estonia    2%\n",
       "59                  Finland    7%\n",
       "60                   France   81%\n",
       "64                  Germany  100%\n",
       "66                   Greece   13%\n",
       "74                  Hungary   12%\n",
       "75                  Iceland    0%\n",
       "80                  Ireland    6%\n",
       "82                    Italy   72%\n",
       "86               Kazakhstan   22%\n",
       "92                   Latvia    2%\n",
       "97            Liechtenstein    0%\n",
       "98                Lithuania    3%\n",
       "99               Luxembourg    1%\n",
       "105                   Malta    1%\n",
       "111                 Moldova    3%\n",
       "112                  Monaco    0%\n",
       "114              Montenegro    1%\n",
       "121             Netherlands   21%\n",
       "127         North Macedonia    2%\n",
       "128                  Norway    6%\n",
       "137                  Poland   46%\n",
       "138                Portugal   12%\n",
       "140                 Romania   23%\n",
       "147              San Marino    0%\n",
       "151                  Serbia    8%\n",
       "155                Slovakia    7%\n",
       "156                Slovenia    3%\n",
       "162                   Spain   57%\n",
       "166                  Sweden   12%\n",
       "167             Switzerland   10%\n",
       "182                 Ukraine   50%\n",
       "184          United Kingdom   80%\n",
       "189            Vatican City    0%"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bm = world.loc[world['name']=='Germany', ['population']].values[0]\n",
    "(world.loc[world['continent']=='Europe', :]\n",
    "    .assign(pct=round(100*world['population']/bm, 0).astype(int).astype(str)+'%')\n",
    "    .loc[:, ['name', 'pct']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Bigger than every country in Europe\n",
    "\n",
    "Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)\n",
    "\n",
    "We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name\n",
       "35           China\n",
       "84           Japan\n",
       "185  United States"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bm = world.loc[world['continent']=='Europe', 'gdp'].max()\n",
    "world.loc[world['gdp']>bm, ['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Largest in each continent\n",
    "\n",
    "**Find the largest country (by area) in each continent, show the continent, the name and the area:**\n",
    "\n",
    "```sql\n",
    "SELECT continent, name, population FROM world x\n",
    "  WHERE population >= ALL\n",
    "    (SELECT population FROM world y\n",
    "        WHERE y.continent=x.continent\n",
    "          AND population>0)\n",
    "```\n",
    "\n",
    "> __The above example is known as a correlated or synchronized sub-query.__   \n",
    "> \n",
    "> Using correlated subqueries\n",
    "> A correlated subquery works like a nested loop: the subquery only has access to rows related to a single > record at a time in the outer query. The technique relies on table aliases to identify two different uses of the same table, one in the outer query and the other in the subquery.\n",
    "> \n",
    "> One way to interpret the line in the **WHERE** clause that references the two table is _“… where the correlated values are the same”._\n",
    "> \n",
    "> In the example provided, you would say _“select the country details from world where the population is greater than or equal to the population of all countries where the continent is the same”._"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>continent</th>\n",
       "      <th>name</th>\n",
       "      <th>area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Africa</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2381741.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Oceania</td>\n",
       "      <td>Australia</td>\n",
       "      <td>7692024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>South America</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>8515767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>North America</td>\n",
       "      <td>Canada</td>\n",
       "      <td>9984670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>9596961.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Caribbean</td>\n",
       "      <td>Cuba</td>\n",
       "      <td>109884.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Europe</td>\n",
       "      <td>Kazakhstan</td>\n",
       "      <td>2724900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>Federated States of,Oceania</td>\n",
       "      <td>Micronesia</td>\n",
       "      <td>702.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>Eurasia</td>\n",
       "      <td>Russia</td>\n",
       "      <td>17125242.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        continent        name        area\n",
       "2                          Africa     Algeria   2381741.0\n",
       "8                         Oceania   Australia   7692024.0\n",
       "23                  South America      Brazil   8515767.0\n",
       "31                  North America      Canada   9984670.0\n",
       "35                           Asia       China   9596961.0\n",
       "43                      Caribbean        Cuba    109884.0\n",
       "86                         Europe  Kazakhstan   2724900.0\n",
       "110   Federated States of,Oceania  Micronesia       702.0\n",
       "141                       Eurasia      Russia  17125242.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx = world.groupby('continent')['area'].transform(max)==world['area']\n",
    "pd.DataFrame(world.loc[idx, ['continent', 'name', 'area']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. First country of each continent (alphabetically)\n",
    "\n",
    "**List each continent and the name of the country that comes first alphabetically.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>continent</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Asia</td>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Europe</td>\n",
       "      <td>Albania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Africa</td>\n",
       "      <td>Algeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Caribbean</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>South America</td>\n",
       "      <td>Argentina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Eurasia</td>\n",
       "      <td>Armenia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Oceania</td>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>North America</td>\n",
       "      <td>Belize</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>Federated States of,Oceania</td>\n",
       "      <td>Micronesia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        continent                 name\n",
       "0                            Asia          Afghanistan\n",
       "1                          Europe              Albania\n",
       "2                          Africa              Algeria\n",
       "5                       Caribbean  Antigua and Barbuda\n",
       "6                   South America            Argentina\n",
       "7                         Eurasia              Armenia\n",
       "8                         Oceania            Australia\n",
       "17                  North America               Belize\n",
       "110   Federated States of,Oceania           Micronesia"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx = world.groupby('continent')['name'].transform(min)==world['name']\n",
    "pd.DataFrame(world.loc[idx, ['continent', 'name']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Difficult Questions That Utilize Techniques Not Covered In Prior Sections\n",
    "\n",
    "**Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>continent</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>96453.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Bahamas</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>385340.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Barbados</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>287025.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Cuba</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>11209628.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Dominica</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>71808.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Dominican Republic</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>10358320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Grenada</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>112003.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Haiti</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>11577779.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Jamaica</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>2726667.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>Micronesia</td>\n",
       "      <td>Federated States of,Oceania</td>\n",
       "      <td>101351.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Saint Lucia</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>178696.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>Trinidad and Tobago</td>\n",
       "      <td>Caribbean</td>\n",
       "      <td>1363985.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    name                     continent  population\n",
       "5    Antigua and Barbuda                     Caribbean     96453.0\n",
       "11               Bahamas                     Caribbean    385340.0\n",
       "14              Barbados                     Caribbean    287025.0\n",
       "43                  Cuba                     Caribbean  11209628.0\n",
       "48              Dominica                     Caribbean     71808.0\n",
       "49    Dominican Republic                     Caribbean  10358320.0\n",
       "67               Grenada                     Caribbean    112003.0\n",
       "72                 Haiti                     Caribbean  11577779.0\n",
       "83               Jamaica                     Caribbean   2726667.0\n",
       "110           Micronesia   Federated States of,Oceania    101351.0\n",
       "144          Saint Lucia                     Caribbean    178696.0\n",
       "176  Trinidad and Tobago                     Caribbean   1363985.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bm = world.loc[world['population']>2.5e7, 'continent']\n",
    "world.loc[~world['continent'].isin(bm), ['name', 'continent', 'population']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "\n",
    "**Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               0\n",
       "0  South America\n",
       "1         Brazil"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how many neighbors are there for each continent\n",
    "bm = pd.DataFrame(world.groupby('continent')['name'].count()-1)\n",
    "# compare with tripple population\n",
    "a = world.set_index('continent').join((world\n",
    "    .assign(popl3=3*world['population'])\n",
    "    .loc[:,['continent', 'name', 'popl3']]\n",
    "    .set_index('continent')), on='continent', how='outer', rsuffix='_y')\n",
    "a = a.loc[a['population'] > a['popl3'], ['name', 'name_y']]\n",
    "b = a.groupby([a.index, a.name]).count()\n",
    "b = b.merge(bm, left_index=True, right_index=True)\n",
    "pd.DataFrame(b.loc[b['name']==b['name_y'], :].index[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
